package cn.kmsoft.common.utils;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author : cqwensten
 * @description : todo
 * @email : cqwensten@163.com
 * @date : 2021/11/29
 */
public class ExcelUtil {
    private static final Logger logger = LogManager.getLogger(ExcelUtil.class);


    public static String EXCEL_2007_ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    public static String EXCEL_2003_ContentType = "application/vnd.ms-excel";
    public static String EXCEL_2003_FileExtType = "xls";
    public static String EXCEL_2007_FileExtType = "xlsx";


    /**
     * readExcelBySheetIndex 读取xls文件内容
     *
     * @param xlspath       xls文件路径
     * @param sheetIndex    sheet索引号，从0开始
     * @param startRowIndex 数据开始行数，主要是针对跳过表头行问题
     * @return
     * @throws IOException
     */
    @SuppressWarnings("resource")
    public static String[][] readExcelBySheetIndex(String xlspath, int sheetIndex, int startRowIndex) throws IOException {
        List<String[]> result = new ArrayList<String[]>();

        File file = new File(xlspath);
        FileInputStream fStream = new FileInputStream(file);
        Workbook wb = null;
        //根据inputStream建立一个Excel对象
		/*
		String contentType = ((MultipartFile)file).getContentType();//内容类型
		if (EXCEL_2003_ContentType.equals(contentType)){
			wb = (Workbook) new HSSFWorkbook(fStream);	//2003
		}else if(EXCEL_2007_ContentType.equals(contentType)) {
			wb = (Workbook) new XSSFWorkbook(fStream);	//2007
		}
		*/
        String fileExt = FileUtil.getExtensionName(xlspath);
        if (EXCEL_2003_FileExtType.equalsIgnoreCase(fileExt)) {
            wb = (Workbook) new HSSFWorkbook(fStream);    //2003
        } else if (EXCEL_2007_FileExtType.equalsIgnoreCase(fileExt)) {
            wb = (Workbook) new XSSFWorkbook(fStream);    //2007
        }

        int sheetCount = wb.getNumberOfSheets();
        if (sheetCount <= sheetIndex) {
            return null;
        }

        Cell cell = null;
        Sheet st = wb.getSheetAt(sheetIndex);

        int rowSize = st.getLastRowNum();
        int maxColumnSize = 1;
        for (int rowIndex = startRowIndex; rowIndex <= rowSize; rowIndex++) {
            Row row = st.getRow(rowIndex);
            if (null == row) {
                continue;
            }
            //存储每一行的值
            boolean hasValue = false;
            int columnSize = row.getLastCellNum() + 1;
            if (columnSize > maxColumnSize) {
                maxColumnSize = columnSize;
            }

            String[] valuesOfRow = new String[columnSize];
            Arrays.fill(valuesOfRow, "");
            for (short columnIndex = 0; columnIndex < columnSize; columnIndex++) {
                cell = row.getCell(columnIndex);
                //获取cell值
                String value = getCellValueByCell(cell);
                valuesOfRow[columnIndex] = rightTrim(value);
                hasValue = true;
            }

            if (hasValue) {
                result.add(valuesOfRow);
            }
        }

        fStream.close();
        String[][] returnArray = new String[result.size()][maxColumnSize];
        for (int i = 0; i < returnArray.length; i++) {
            returnArray[i] = (String[]) result.get(i);
        }
        return returnArray;

    }

    /**
     * excel文件导入
     *
     * @param file
     * @param ignoreRows 需要忽略掉的行数
     * @return
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static String[][] importFile(MultipartFile file, int ignoreRows)
            throws FileNotFoundException, IOException {
        //将excel视为一个二维的 String[][] 字符串数组
        List<String[]> result = new ArrayList<String[]>();

        String contentType = file.getContentType();     //内容类型
        InputStream stream = file.getInputStream();  //输入流
        Workbook wb = null;
        //根据inputStream建立一个Excel对象
        if (EXCEL_2003_ContentType.equals(contentType)) {
            wb = (Workbook) new HSSFWorkbook(stream);    //2003
        } else if (EXCEL_2007_ContentType.equals(contentType)) {
            wb = (Workbook) new XSSFWorkbook(stream);    //2007
        }
        int columnSize = 0;
        Cell cell = null;
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
            Sheet st = wb.getSheetAt(sheetIndex);
            for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
                Row row = st.getRow(rowIndex);
                if (null == row) {
                    continue;
                }
                int tempColumnSize = row.getLastCellNum() + 1;
                if (tempColumnSize > columnSize) {
                    columnSize = tempColumnSize;
                }

                //存储每一行的值
                String[] values = new String[columnSize];
                Arrays.fill(values, "");

                boolean hasValue = false;
                for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
                    String value = "";
                    cell = row.getCell(columnIndex);
                    if (null != cell) {
                        CellType cellType = cell.getCellType();
                        switch (cellType) {
                            case FORMULA:
                                // 公式生成的数据
                                if (!"".equals(cell.getStringCellValue())) {
                                    value = cell.getStringCellValue();
                                } else {
                                    value = cell.getNumericCellValue() + "";
                                }
                                break;
                            case STRING:
                                value = cell.getStringCellValue();
                                break;
                            case NUMERIC:
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    if (null != date) {
                                        value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                                    } else {
                                        value = "";
                                    }
                                } else {
                                    DecimalFormat df = new DecimalFormat("0.#######");
                                    //value = df.format(cell.getNumericCellValue());
                                    value = String.valueOf(df.format(cell.getNumericCellValue()));
                                }
                                break;
                            case BOOLEAN:
                                value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                                break;
                            case BLANK:
                                value = "";
                                break;
                            case ERROR:
                                value = "";
                                break;

                            default:
                                value = "";
                        }
                    }
                    if (columnIndex == 0 && value.trim().equals("")) {
                        break;
                    }
                    values[columnIndex] = rightTrim(value);
                    hasValue = true;
                }

                if (hasValue) {
                    result.add(values);
                }
            }
        }
        stream.close();
        String[][] returnArray = new String[result.size()][columnSize];
        for (int i = 0; i < returnArray.length; i++) {
            returnArray[i] = (String[]) result.get(i);
        }
        return returnArray;
    }

    /**
     * excel文件导入
     *
     * @param file
     * @param ignoreRows 需要忽略掉的行数
     * @return
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static String[][] importFile(MultipartFile file, int sheetIndex, int ignoreRows, Integer colCnt) throws FileNotFoundException, IOException {
        List<String[]> result = new ArrayList<String[]>();
        String contentType = file.getContentType();     //内容类型
        InputStream stream = file.getInputStream();  //输入流
        Workbook wb = null;
        //根据inputStream建立一个Excel对象
        if (EXCEL_2003_ContentType.equals(contentType)) {
            wb = (Workbook) new HSSFWorkbook(stream);    //2003
        } else if (EXCEL_2007_ContentType.equals(contentType)) {
            wb = (Workbook) new XSSFWorkbook(stream);    //2007
        }

        Cell cell = null;
        int columnSize = 0;
        Sheet st = wb.getSheetAt(sheetIndex);
        for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
            Row row = st.getRow(rowIndex);
            if (null == row) {
                continue;
            }
            //int tempRowSize = row.getLastCellNum() + 1;
            int tempColumnSize = row.getPhysicalNumberOfCells();
            if (colCnt == null) {
                if (tempColumnSize > columnSize) {
                    columnSize = tempColumnSize;
                }
                colCnt = columnSize;
            }
            String[] values = new String[colCnt];
            Arrays.fill(values, "");
            boolean hasValue = false;
            for (short columnIndex = 0; columnIndex < colCnt; columnIndex++) {
                String value = "";
                cell = row.getCell(columnIndex);
                if (null != cell) {
                    switch (cell.getCellType()) {
                        case STRING:
                            value = cell.getStringCellValue();
                            break;
                        case NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                Date date = cell.getDateCellValue();
                                if (null != date) {
                                    value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                                } else {
                                    value = "";
                                }
                            } else {
                                DecimalFormat df = new DecimalFormat("0.#######");
                                //value = df.format(cell.getNumericCellValue());
                                value = String.valueOf(df.format(cell.getNumericCellValue()));
                            }
                            break;
                        case BOOLEAN:
                            value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                            break;
                        case FORMULA:
                            // 公式生成的数据
                            if (!"".equals(cell.getStringCellValue())) {
                                value = cell.getStringCellValue();
                            } else {
                                value = cell.getNumericCellValue() + "";
                            }
                            break;
                        case BLANK:
                            break;
                        case ERROR:
                            value = "";
                            break;

                        default:
                            value = "";
                    }
                }
                if (columnIndex == 0 && value.trim().equals("")) {
                    break;
                }
                values[columnIndex] = rightTrim(value);
                hasValue = true;
            }
            if (hasValue) {
                result.add(values);
            }
        }

        stream.close();
        String[][] returnArray = new String[result.size()][colCnt];
        for (int i = 0; i < returnArray.length; i++) {
            returnArray[i] = (String[]) result.get(i);
        }
        return returnArray;
    }

    //获取单元格各类型值，返回字符串类型
    private static String getCellValueByCell(Cell cell) {
        //判断是否为null或空串
        if (cell == null || cell.toString().trim().equals("")) {
            return "";
        }
        String cellValue = "";
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case FORMULA: //表达式类型
                cellValue = cell.getStringCellValue().trim();
                cellValue = StringUtil.isNullOrEmpty(cellValue) ? "" : cellValue;
                break;
            case STRING: //字符串类型
                cellValue = cell.getStringCellValue().trim();
                cellValue = StringUtil.isNullOrEmpty(cellValue) ? "" : cellValue;
                break;
            case BOOLEAN:  //布尔类型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case NUMERIC: //数值类型
                if (HSSFDateUtil.isCellDateFormatted(cell)) {  //判断日期类型
                    cellValue = DateUtil.toDateStr(cell.getDateCellValue());
                } else {  //否
                    cellValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
                }
                break;
            default: //其它类型，取空串吧
                cellValue = "";
                break;
        }
        return cellValue;
    }

    /**
     * 去除字符串右侧空格(直接使用trim()将不能支持中间存在空格的单元格)
     *
     * @param str
     * @return
     */
    public static String rightTrim(String str) {
        if (str == null) {
            return "";
        }
        int length = str.length();
        for (int i = length - 1; i >= 0; i--) {
            if (0x20 != str.charAt(i)) {
                break;
            }

            length--;
        }
        return str.substring(0, length);
    }

    public static HSSFSheet parseXlsfile(String xlspath, int sheetIndex) throws IOException, InvalidFormatException {
        File xlsFile = new File(xlspath);
        FileInputStream fStream = new FileInputStream(xlsFile);

        //XSSFWorkbook  workbook = new XSSFWorkbook(fStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fStream);
        HSSFSheet sheet = null;
        int sheetCount = workbook.getNumberOfSheets();
        if (sheetCount > sheetIndex) {
            sheet = workbook.getSheetAt(sheetIndex);
        }

        workbook.close();
        return sheet;
    }

    public static XSSFSheet parseXlsxfile(String xlsxpath, int sheetIndex) throws IOException, InvalidFormatException {
        File xlsFile = new File(xlsxpath);
        FileInputStream fStream = new FileInputStream(xlsFile);

        XSSFWorkbook workbook = new XSSFWorkbook(fStream);

        XSSFSheet sheet = null;
        int sheetCount = workbook.getNumberOfSheets();
        if (sheetCount > sheetIndex) {
            sheet = workbook.getSheetAt(sheetIndex);
        }

        workbook.close();
        return sheet;
    }


    /**
     * <b>描述:</b>列表数据写到excel的sheet里 <br/>
     * 1、dataList是map的列表数据，<br/>
     * 2、fieldNames：map里的对应字段，有顺序<br/>
     * 3、startRow：从哪行开始（0是第一行）<br/>
     * 4、startCol：从哪列开始（0是第一列）<br/>
     *
     * @param sheet
     * @param startRow
     * @param startCol
     * @param fieldNames
     * @param dataList
     */
    public static void buildSheet(HSSFSheet sheet, int startRow, int startCol, String fieldNames[], List<Map<String, Object>> dataList) {
        int orignStartCol = startCol;
        for (Map<String, Object> map : dataList) {
            HSSFRow row = sheet.createRow(startRow);
            for (String fieldName : fieldNames) {
                Object obj = map.get(fieldName);
                if (obj != null) {
                    HSSFCell cell = row.createCell(startCol);
                    String val = obj.toString();
                    cell.setCellValue(val);
                }
                startCol++;
            }
            startCol = orignStartCol;
            startRow++;
        }
    }


    /**
     * lwj
     *
     * @param hwb
     * @param hs
     * @param heads
     */
    public static void createHead(HSSFWorkbook hwb, HSSFSheet hs, String[] heads) {
        HSSFRow row = hs.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        for (int i = 0; i <= heads.length; i++) {
            hs.setColumnWidth(i, 15 * 256);
        }
        //设置为居中加粗,格式化时间格式
        HSSFCellStyle style = hwb.createCellStyle();
        HSSFFont font = hwb.createFont();
        font.setBold(true);
        style.setFont(font);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
        //创建表头名称
        HSSFCell cell;
        for (int j = 0; j < heads.length; j++) {
            cell = row.createCell(j);
            cell.setCellValue(heads[j]);
            cell.setCellStyle(style);
        }
    }

    /**
     * lwj
     *
     * @param hs
     * @param datas
     * @param colums
     */
    public static void insertData(HSSFSheet hs, List<Map<String, Object>> datas, String[] colums) {
        try {
            int rowNum = 1;
            for (int i = 0; i < datas.size(); i++) {
                HSSFRow row = hs.createRow(rowNum);
                for (int j = 0; j < colums.length; j++) {
                    String val = datas.get(i).get(colums[j]) + "";
                    row.createCell(j).setCellValue("null".equals(val) ? "" : val);
                }
                rowNum++;
            }
            logger.info("表格写入数据成功！");
        } catch (Exception e) {
            logger.info("表格写入数据失败！");
            e.printStackTrace();
        }
    }

    /**
     * lwj
     *
     * @param response
     * @param workbook
     * @param fileName
     */
    public static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
        try {
            //清空response
            response.reset();
            //设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            OutputStream os = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=gb2312");
            //将excel写入到输出流中
            workbook.write(os);
            os.flush();
            os.close();
            logger.info("设置浏览器下载成功！");
        } catch (Exception e) {
            logger.info("设置浏览器下载失败！");
            e.printStackTrace();
        }

    }
}
